Plan and manage the technical design, development and implementation of new and/or enhanced business systems applications by:

a) identifying and estimating required technical activities;

b) defining technical work assignments and priorities;

c) monitoring technical work; recommending/implementing solutions to scheduling, logistical and/or operational problems;

d) ensuring involvement of all appropriate technical specialists to meet business requirements;

e) lead unit, integration and system tests to evaluate development outcomes, ensure conformity with user client needs and compatibility with policies and standards;

f) ensure quality control performed on all deliverables before handover to Business Consultant;

g) ensure development of procedures to facilitate data conversions, data backup and systems security;

h) monitor software development work and certify deliverables; and

i) plan, lead, and otherwise undertake the development of integrated proof of concept solutions, to verify emerging technologies, products, services, and methodologies, to ensure government understands technical opportunities/exposures, and to ensure deployment minimizes associated risks and threats.

The objective of this project is to document the metadata and data flows within our existing SSIS ETL processes and SSIS objects, specifically focusing on the Mental Health and Substance Use (MHSU) Minimum Reporting Requirements (MRR) dataset. This entails reverse-engineering all SSIS packages associated with the MHSU MRR dataset to gain a comprehensive understanding of the data transformation and loading processes. The goal is to enhance the maintainability, scalability, and efficiency of data integration for the MHSU MRR dataset and to ensure compliance with data governance policies. 2.2 Background The MHSU MRR submission has been in place for eight (8) years. The specifications do get revised by the Ministry of Health (MoH) periodically. Island Health’s toolset has changed dramatically, and additional internal requirements have been identified, since the technical submission was established. 2.3 Scope The anticipated term of this opportunity is from an available start date of May 15th, 2024 to an anticipated end date of August 15th, 2024, with an option to extend for up to 1 year. All proposed Consultants must be available to start within ten (10) business days of award of this SOW. The scope of this project encompasses the following key areas: 1. MHSU MRR Dataset Data Flows Documentation: • Map out and document all data flows within the SSIS packages related to the MHSU MRR dataset, including source and destination data stores, transformations, and conditional logic. 2. Metadata Documentation for MHSU MRR Dataset: • Document metadata for all SSIS objects related to the MHSU MRR dataset, including package configurations, variables, parameters, connections, and dependencies. 3. Reverse-Engineering SSIS Packages for MHSU MRR Dataset: • Decompile and analyze all SSIS packages related to the MHSU MRR dataset to understand the underlying logic and integrations. • Document findings and recommendations for optimization or refactoring specific to the MHSU MRR dataset. 6 4. Data Governance Compliance for MHSU MRR Dataset: • Ensure all documentation and SSIS practices align with organizational data governance and data quality standards, with a specific focus on the MHSU MRR dataset. 5. Knowledge Transfer for MHSU MRR Dataset: • Develop comprehensive guides and training materials based on the documentation to facilitate knowledge transfer and training of new staff on the MHSU MRR dataset. The deliverables for this project will include: 1. Detailed documentation of SSIS data flows and metadata specific to the MHSU MRR dataset. 2. A report on the findings from the reverse-engineering process of the SSIS packages for the MHSU MRR dataset, including potential areas for optimization. 3. A set of best practices and recommendations for SSIS package development and maintenance with a focus on the MHSU MRR dataset. 4. Training materials and guides for onboarding new team members on the MHSU MRR dataset. 5. A final project report summarizing methodologies, findings, and recommendations for the MHSU MRR dataset. A sample timeline for this project is as follows, but will shift slightly based on the consultant start date: 1. Project Planning and Kickoff: [May 1-3, 2024] 2. Phase 1 - Data Flow and Metadata Documentation: [May 6-10] 3. Phase 2 - Reverse-Engineering of SSIS Packages: [May 13-31] 4. Phase 3 - Development of Guides and Training Materials: [June 3-10] 5. Review and Finalization: [June 11-14] Please note that the above is a sample timeline, but this will be adjusted based on the actual start date of the project. This project requires close collaboration with Island Health's Decision Support Teams and stakeholders to ensure that the recommendations are practical, feasible, and align with the organization's strategic direction and operational needs. The successful proponent will have proven experience in conducting similar assessments and developing technology roadmaps for large organizations, preferably within the public sector or healthcare industry. The maximum number of consultant hours is anticipated to be two hundred fifty (250) with the possibility of adjusting if needed. 3. QUALIFICATIONS 3.1 Consultant Qualifications The Application Designer/Developer Level 2 is required to have the following skills and experience: Plan and manage the technical design, development and implementation of new and/or enhanced business systems applications by: a) identifying and estimating required technical activities; b) defining technical work assignments and priorities; c) monitoring technical work; recommending/implementing solutions to scheduling, logistical and/or operational problems; d) ensuring involvement of all appropriate technical specialists to meet business requirements; e) lead unit, integration and system tests to evaluate development outcomes, ensure conformity with user client needs and compatibility with policies and standards; 7 f) ensure quality control performed on all deliverables before handover to Business Consultant; g) ensure development of procedures to facilitate data conversions, data backup and systems security; h) monitor software development work and certify deliverables; and i) plan, lead, and otherwise undertake the development of integrated proof of concept solutions, to verify emerging technologies, products, services, and methodologies, to ensure government understands technical opportunities/exposures, and to ensure deployment minimizes associated risks and threats. In addition to the skills and experience set out above, the Proponents should demonstrate that the proposed Consultant(s) have the following: • Able to meet either physically on Vancouver Island, or remotely using virtual meetings utilizing Microsoft Teams, • Be available during Island Health business hours, which are in Pacific Standard Time, • Contractor will need to be able to work in a team environment. 3.1.1 Response Guidelines Proponents should complete all information solicited in the VMS candidate submission link, and, in addition, provide the following: • A current resume, and • written proposal. 3.2 Bill Rate Consultant(s) are able to submit their bill rate based on proposal submission: 1. A fixed hourly rate for the duration of the engagement for all consultants attached to this engagement, with invoicing completed monthly. Any consultants utilized as part of this engagement will be expected to log their time in the Flextrack timesheet system. Rates will be deemed to be (a) in Canadian dollars; and (b) exclusive of applicable taxes.